﻿Create PROCEDURE [dbo].[proc_Project_Getlist_fqqd]
	(
		@CompanyId int,
		@StartDate nvarchar(10),
		@EndDate nvarchar(10),
		@Audit int,
		@Stext nvarchar(50),
		@StartIndex int,
		@EndIndex INT
	)
AS
Begin
	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime, @StartDate + ' 00:00:00')
	Set @eRq = Convert(Datetime, @EndDate + ' 23:59:59')
	
	;WITH list as (
		SELECT ROW_NUMBER() OVER (ORDER BY CusName)AS Row	,* from ( select OrderId,CusName,SUM(a) AS a FROM (
		Select id, OrderId
		,CusName
		,(SalePrice-CostPrice) AS a
		From Project
		WHERE  ( CompanyId=@CompanyId) and CreateDate>= @sRq and CreateDate<=@eRq
			and SalePrice<>CostPrice AND ProductId not IN (22,29,33) AND NOE_Flag=1
		UNION 
		SELECT Order_Change_Hedge.id,Order_Change_Hedge.OId AS OrderId,Order_Change_Hedge.CusName,dch_xzml+dch_xfml AS a
		  FROM Order_Change_Hedge inner join [Order] o on Order_Change_Hedge.oid = o.oid and Order_Change_Hedge.CompanyId=@companyId and UpTime>= @sRq and UpTime<=@eRq
		  AND dch_xzml+dch_xfml!=0 
			AND o.companyId=@CompanyId AND o.NOE_Flag=1 	
				) AS ab GROUP BY OrderId,CusName ) AS nb
			)
		
		Select *,(Select Count(1) From list) AS DataCount
			,(Select Sum(a) From list) AS CostPrice_SUM 
		From list Where Row between @StartIndex and @EndIndex  
End
RETURN
